<?php
class MyRequest{
	
	function MyRequest (){
		
	}
	
	
	/**
	*	Funcion responsable de agregar el request al banco.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@return:
	*/	
	function addRequest($data){
		$sql = "INSERT INTO request (req_code, case_code, baco_code, ban_code, req_date, req_status,loan_number) VALUES 
			(NULL, '".$data->case_code."', '".$data->baco_code."', '".$data->ban_code."', '".date("Y-m-d H:i:s")."', 'Active','".$data->loan_number."');";

		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}	
	
	
	/**
	*	Funcion responsable de agregar el nombre del banco.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@return:
	*/	
	function addBank($data){
		$sql = "INSERT INTO bank ( ban_code, ban_name, ban_date_create ) VALUES (NULL, '".$data->ban_name."', '".date("Y-m-d H:i:s")."');";	

		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}
	
	/**
	*	Funcion responsable de agregar el nombre del Contacto del banco.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@return:
	*/	
	function addContactBank($data){
		$sql = "INSERT INTO bank_contact (baco_code, ban_code, baco_name, baco_note) VALUES (NULL, '".$data->ban_code."', '".$data->baco_name."', '');";
		if(mysql_query($sql)){		
			return mysql_insert_id ();
		}else
			return false;
	}
	
	/**
	*	Funcion responsable de agregar el nombre del Contacto del banco.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@return:
	*/	
	function getBank($ban_code){
		$sql = "SELECT b.ban_code, b.ban_name FROM bank b WHERE b.ban_code=".$ban_code.";";
		if ( $result = mysql_query($sql) ){
			if ( $row = mysql_fetch_array ($result) ){
				$data->ban_code = $row['ban_code'];
				$data->ban_name = $row['ban_name'];
				
				return $data;
			}
			else
				return false;				
		}else
			return false;
	}
	
	/**
	*	Funcion responsable de listar los nombres de los bancos.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@return:
	*/
	function bankList(){
		$sql ="SELECT ban_code, ban_name FROM bank;";
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->ban_code 		= $row["ban_code"];
			$data[$i]->ban_name 		= $row["ban_name"];	
							
			$i++;
		}			
		return $data;
	}
	
	/**
	*	Funcion responsable de listar los contactos de los bancos.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@return:
	*/
	function bankContactList($ban_code){
		$sql = "SELECT bc.baco_code, bc.baco_name FROM bank_contact bc WHERE bc.ban_code=".$ban_code;
		$consult = mysql_query($sql);
		$data = array();
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->baco_code 		= $row["baco_code"];
			$data[$i]->baco_name 		= $row["baco_name"];	
							
			$i++;
		}			
		return $data;
	}	
		
	/**
	*	Esta funcion es la encargada de listar los request de los clientes a los bancos.
	*	@autor: JJR
	*	@date: 01/28/2012
	*	@parameters: 
	*	@return:
	*/
	function requestList($search='',$order_by='', $inicio = 0,$cantidad = 0){
		$where = '';
		if($search != '')
			$where = "  WHERE (use_name like '%".$search."%' OR use_lastname like '%".$search."%' OR cli_name like '%".$search."%'
					OR cli_lastname like '%".$search."%' OR case_number like '%".$search."%' OR ban_name like '%".$search."%' 
					OR loan_number like '%".$search."%' )";
		
		if ($order_by == '')
			$order_by = ' ORDER BY case_number DESC ';		
		
		$where_user = '';
		if ( $_SESSION['use_type'] == 'Adviser' )
			$where_user = "  AND use_type='Adviser' ";
		
		$sql = "
		SELECT t3.req_code, t3.case_code, t3.baco_code, t3.ban_code, t3.req_date, t3.req_status,t3.loan_number, t3.ban_name, t3.baco_name, t3.cli_code, t3.use_code, t3.case_number, t3.cli_name,
			t3.cli_lastname, u.use_name, u.use_lastname FROM
		(SELECT t2.req_code, t2.case_code, t2.baco_code, t2.ban_code, t2.req_date, t2.req_status,t2.loan_number, t2.ban_name, t2.baco_name, t2.cli_code, t2.use_code, t2.case_number, c.cli_name,
			c.cli_lastname FROM
		(SELECT t1.req_code, t1.case_code, t1.baco_code, t1.ban_code, t1.req_date, t1.req_status,t1.loan_number, t1.ban_name, t1.baco_name, mc.cli_code, mc.use_code, mc.case_number FROM
		(SELECT t.req_code, t.case_code, t.baco_code, t.ban_code, t.req_date, t.req_status,t.loan_number, t.ban_name, bc.baco_name FROM
		(SELECT r.req_code, r.case_code, r.baco_code, r.ban_code, r.req_date, r.req_status,r.loan_number, b.ban_name FROM request r LEFT JOIN bank b ON r.ban_code = b.ban_code) t
		LEFT JOIN bank_contact bc ON t.baco_code=bc.baco_code) t1
		JOIN my_case mc ON t1.case_code=mc.case_code) t2
		JOIN client c ON t2.cli_code=c.cli_code WHERE c.cli_status <> 'Deleted') t3
		JOIN user u ON t3.use_code=u.use_code".$where." ".$where_user."  ".$order_by."";
		
		if ( !($inicio == 0 && $cantidad == 0 ) )
			$sql .= ' LIMIT '.$inicio.', '.$cantidad.';';
		
		$consult  = mysql_query($sql);
		$data;
		$i = 0;
		while($row = mysql_fetch_array($consult)){
			$data[$i]->req_code = $row['req_code'];
			$data[$i]->case_code = $row['case_code'];
			$data[$i]->baco_code = $row['baco_code'];
			$data[$i]->ban_code = $row['ban_code'];
			$data[$i]->req_date = $row['req_date'];
			$data[$i]->req_status = $row['req_status'];
			$data[$i]->ban_name = $row['ban_name'];
			$data[$i]->baco_name = $row['baco_name'];
			$data[$i]->cli_code = $row['cli_code'];
			$data[$i]->case_number = $row['case_number'];
			$data[$i]->cli_name = $row['cli_name'];
			$data[$i]->cli_lastname = $row['cli_lastname'];
			$data[$i]->use_name = $row['use_name'];
			$data[$i]->use_lastname = $row['use_lastname'];
			$data[$i]->loan_number = $row['loan_number'];
			$i++;				
		}
		return $data;
	}	
	
	/**
	*	@autor: JJR
	*	@date: 01/28/2012
	*	Funcion encargada de contar la cantidad de request.
	*	@return:
	*/
	function countRequest($search = ''){
		$where = '';
		if($search != '')
			$where = "  WHERE (use_name like '%".$search."%' OR use_lastname like '%".$search."%' OR cli_name like '%".$search."%'
					OR cli_lastname like '%".$search."%' OR case_number like '%".$search."%' OR ban_name like '%".$search."%' 
					OR loan_number like '%".$search."%' )";
		
		$where_user = '';
		if ( $_SESSION['use_type'] == 'Adviser' )
			$where_user = "  AND use_type='Adviser' ";
		
		$sql = "
		SELECT COUNT(*) AS cant FROM
		(SELECT t2.req_code, t2.case_code, t2.baco_code, t2.ban_code, t2.req_date, t2.req_status,t2.loan_number, t2.ban_name, t2.baco_name, t2.cli_code, t2.use_code, t2.case_number, c.cli_name,
			c.cli_lastname FROM
		(SELECT t1.req_code, t1.case_code, t1.baco_code, t1.ban_code, t1.req_date, t1.req_status,t1.loan_number, t1.ban_name, t1.baco_name, mc.cli_code, mc.use_code, mc.case_number FROM
		(SELECT t.req_code, t.case_code, t.baco_code, t.ban_code, t.req_date, t.req_status,t.loan_number, t.ban_name, bc.baco_name FROM
		(SELECT r.req_code, r.case_code, r.baco_code, r.ban_code, r.req_date, r.req_status,r.loan_number, b.ban_name FROM request r LEFT JOIN bank b ON r.ban_code = b.ban_code) t
		LEFT JOIN bank_contact bc ON t.baco_code=bc.baco_code) t1
		JOIN my_case mc ON t1.case_code=mc.case_code) t2
		JOIN client c ON t2.cli_code=c.cli_code WHERE c.cli_status <> 'Deleted') t3
		JOIN user u ON t3.use_code=u.use_code ".$where." ".$where_user.";"; 
	
		$consulta = mysql_query($sql);
		$row = mysql_fetch_array($consulta);
			
		return $row['cant'];
	}
	
	/**
	*	@autor: JSL
	*	@date: 01/02/2012
	*	Funcion encargada de obtener los datos de un request.
	*	@return:
	*/	
	function getRequestData($req_code){
		$sql = "
		SELECT t3.req_code, t3.case_code, t3.baco_code, t3.ban_code, t3.req_date, t3.req_status,t3.loan_number, t3.ban_name, t3.baco_name, t3.cli_code, t3.use_code, t3.case_number, t3.cli_name,
			t3.cli_lastname, u.use_name, u.use_lastname FROM
		(SELECT t2.req_code, t2.case_code, t2.baco_code, t2.ban_code, t2.req_date, t2.req_status,t2.loan_number, t2.ban_name, t2.baco_name, t2.cli_code, t2.use_code, t2.case_number, c.cli_name,
			c.cli_lastname FROM
		(SELECT t1.req_code, t1.case_code, t1.baco_code, t1.ban_code, t1.req_date, t1.req_status,t1.loan_number, t1.ban_name, t1.baco_name, mc.cli_code, mc.use_code, mc.case_number FROM
		(SELECT t.req_code, t.case_code, t.baco_code, t.ban_code, t.req_date, t.req_status,t.loan_number, t.ban_name, bc.baco_name FROM
		(SELECT r.req_code, r.case_code, r.baco_code, r.ban_code, r.req_date, r.req_status,r.loan_number, b.ban_name FROM request r LEFT JOIN bank b ON r.ban_code = b.ban_code
			WHERE r.req_code=".$req_code.") t
		LEFT JOIN bank_contact bc ON t.baco_code=bc.baco_code) t1
		JOIN my_case mc ON t1.case_code=mc.case_code) t2
		JOIN client c ON t2.cli_code=c.cli_code) t3
		JOIN user u ON t3.use_code=u.use_code;"; 
		
		if ( $consult = mysql_query($sql) ){
			$row = mysql_fetch_array($consult);
			
			$data->req_code = $row['req_code'] ;
			$data->case_code = $row['case_code'] ;
			$data->baco_code = $row['baco_code'] ;
			$data->ban_code = $row['ban_code'] ;
			$data->req_date = $row['req_date'] ;
			$data->req_status = $row['req_status'] ;
			$data->ban_name = $row['ban_name'] ;
			$data->baco_name = $row['baco_name'] ;
			$data->cli_code = $row['cli_code'] ;
			$data->use_code = $row['use_code'] ;
			$data->case_number = $row['case_number'] ;
			$data->cli_name = $row['cli_name'] ;
			$data->cli_lastname = $row['cli_lastname'] ;
			$data->use_name = $row['use_name'] ;
			$data->use_lastname = $row['use_lastname'] ;
			$data->loan_number = $row['loan_number'] ;
		
			return $data;
		}
		else
			return false;
	}	
	
	/**
	*	@autor: JJR
	*	@date: 01/30/2012
	*	Funcion encargada de Actualizar los Request.
	*	@return:
	*/
	function updateRequest($new_data){
		$sql="UPDATE request SET ban_code = '".$new_data->ban_code."', baco_code = '".$new_data->baco_code."' WHERE req_code =".$new_data->req_code.";";
					
		if(mysql_query($sql)){		
			return true;
		}else
			return false;
	}	
	
	/**
	*	Funcion encargada de Actualizar el status a deleted de un request.
	*	@autor: JJR
	*	@date: 01/30/2012
	*	@parameters: 
	*	@return:
	*/	
	function deleteRequest($req_code){
		$sql="UPDATE `request` SET `req_status` = 'Deleted' WHERE `req_code` =".$req_code; 
		return mysql_query($sql);
	}			
}
?>
